--Drop Database LeaveManagementSystem
--------------------------------------
CREATE DATABASE LeaveManagementSystem
GO
USE LeaveManagementSystem
GO
-------------------------------------

--------------------------------------------------------------------------
--Create table

CREATE TABLE Department
(
	IDDepartment INT PRIMARY KEY IDENTITY,
	DepartmentName NVARCHAR(50) not null
)
GO

CREATE TABLE Position
(
	IDPosition INT PRIMARY KEY,
	PositionName NVARCHAR(50) not null
)
GO

CREATE TABLE Employee 
(
	IDEmp INT PRIMARY KEY IDENTITY,
	FirstName NVARCHAR(25) not null,
	LastName  NVARCHAR(25) not null,
	Gender	BIT not null,
	Birthday DATE not null,
	[Address] NVARCHAR(100) not null,
	Email	NVARCHAR(50),
	Phone	NVARCHAR(15),
	DepartmentID INT,
	PositionID INT not null,
	JoinDate DATE not null,
	UserName NVARCHAR(30) unique not null,
	[Password] NVARCHAR(30) not null,
	StatusEmp BIT not null
)
GO


CREATE TABLE Holiday
(
	IDHoliday INT PRIMARY KEY IDENTITY,
	StartDate DATE not null,
	EndDate DATE not null,
	[Event] NVARCHAR(200)not null
)
GO

CREATE TABLE Configuration
(
	IDConfiguration INT PRIMARY KEY,
	Description NVARCHAR(100) not null,
	Parameter INT not null
)
GO


CREATE TABLE [Application]
(
	IDApplication INT PRIMARY KEY IDENTITY,
	EmpID INT not null,
	StartDate DATE not null,
	EndDate DATE not null,
	Reason NVARCHAR(500) not null,
	Contact NVARCHAR(100),
	[Status] INT not null
)
GO

--------------------------------------------------------------------------
--Create Constraint

ALTER TABLE Employee
ADD CONSTRAINT FK_Employee_Department
FOREIGN KEY (DepartmentID)
REFERENCES Department(IDDepartment) ON DELETE CASCADE
GO

ALTER TABLE Employee
ADD CONSTRAINT FK_Employee_Position
FOREIGN KEY (PositionID)
REFERENCES Position(IDPosition) ON DELETE CASCADE
GO

ALTER TABLE [Application]
ADD CONSTRAINT FK_Application_Employee
FOREIGN KEY (EmpID)
REFERENCES Employee(IDEmp) ON DELETE CASCADE 
GO


-------------------------------------------------------------------------------------------------
--Create Procedure for Department


create procedure sp_InsertDepartment
@DepartmentName nvarchar(50)
as
begin
	insert into Department
	values
		(@DepartmentName)
end
go


create procedure sp_UpdateDepartmentName
@IDDepartment int, @DepartmentName nvarchar(50)
as
begin
	update Department 
	set
		DepartmentName=@DepartmentName
	where IDDepartment=@IDDepartment
end
go


Create procedure sp_DeleteDepartment
@IDDepartment int
as
begin
	delete from Department
	where IDDepartment=@IDDepartment
end
go


create procedure sp_getDepartment
@IDDepartment int
as
begin
	select * from Department where IDDepartment=@IDDepartment
end
go


create procedure sp_getDepartmentList
as
begin
	select * from Department
end
go


-------------------------------------------------------------------------------------------------
--Create Procedure for Position


create procedure sp_InsertPosition
@IDPosition int,@PositionName nvarchar(50)
as
begin
	insert into Position
	values
		(@IDPosition, @PositionName)
end
go


create procedure sp_getPosition
@IDPosition int
as
begin
	select * from Position where IDPosition=@IDPosition
end
go


create procedure sp_getPositionsList
--IDPosition=1: admin
--IDPosition=2: Director
--IDPosition=3: Department Manager
--IDPosition=4: Personnel
as
begin
	select * from Position
	where Position.IDPosition!=1 and Position.IDPosition!=2
end
go


-------------------------------------------------------------------------------------------------
--Create Procedure for Employee


create procedure sp_InsertEmployeeInfo
@FirstName nvarchar(25), @LastName nvarchar (25), @Gender bit, @Birthday date,
@Address nvarchar (100), @Email nvarchar (50), @Phone nvarchar(15),
@DepartmentID int, @PositionID int, @JoinDate date, @UserName nvarchar(30),
@Password nvarchar(30),@StatusEmp bit
as
begin
	insert into Employee 
	values (@FirstName,@LastName,@Gender,@Birthday,@Address,@Email,@Phone,
			@DepartmentID,@PositionID,@JoinDate,@UserName,@Password,@StatusEmp)
end
go


create procedure sp_UpdateEmployeeInfo
@IDEmp int, @FirstName nvarchar (25), @LastName nvarchar(25), @Gender bit,
@Birthday date, @Address nvarchar(100), @Email nvarchar(50), @Phone nvarchar(15),
@DepartmentID int, @PositionID int, @JoinDate date, @UserName nvarchar(30),
@StatusEmp bit
as
begin
	update Employee 
	set
		FirstName=@FirstName,
		LastName=@LastName,
		Gender=@Gender,
		Birthday=@Birthday,
		[Address]=@Address,
		Email=@Email,
		Phone=@Phone,
		DepartmentID=@DepartmentID,
		PositionID=@PositionID,
		JoinDate=@JoinDate,
		UserName=@UserName,
		StatusEmp=@StatusEmp
	where IDEmp=@IDEmp
end
go


create procedure sp_UpdateEmployeePassword
@IDEmp int, @Password nvarchar(50)
as
begin
	update Employee 
	set [Password]=@Password
	where IDEmp=@IDEmp
end
go


create procedure sp_DeleteEmployee
@IDEmp int
as
begin
	delete from Employee
	where IDEmp=@IDEmp
end
go


create procedure sp_CheckLogin
@UserName nvarchar(30),@Password nvarchar(30)
as
begin
	select * from Employee 
	where UserName=@UserName and [Password]=@Password and StatusEmp=1
end
go


create procedure sp_getEmployee
@IDEmp int
as
begin
	select top 1 Employee.*,Department.DepartmentName,Position.PositionName
	from Employee,Department,Position
	where	(Employee.DepartmentID=Department.IDDepartment or Employee.DepartmentID is null)
			and Employee.PositionID=Position.IDPosition
			and Employee.IDEmp=@IDEmp
end
go


create procedure sp_showEmployeesListForAdmin
--IDPosition=1: admin
--IDPosition=2: Director
--IDPosition=3: Department Manager
--IDPosition=4: Personnel
as
begin
	select Employee.*,Department.DepartmentName,Position.PositionName
	from Employee,Department,Position
	where Employee.DepartmentID=Department.IDDepartment and Employee.PositionID=Position.IDPosition
		  and Position.IDPosition!=1 and Position.IDPosition!=2
		  and Employee.StatusEmp!=0
	order by Employee.IDEmp desc
end
go


create view view_getDepartmentManagersList
as
	select * from Employee
	where Employee.PositionID=3
go


create procedure sp_showEmployeesListForDepartmentManager
@IDDepartment int
as
begin
	select Employee.*,Department.DepartmentName,Position.PositionName
	from Employee,Department,Position
	where Employee.DepartmentID=Department.IDDepartment and Employee.PositionID=Position.IDPosition
		  and Department.IDDepartment=@IDDepartment
		  and Employee.StatusEmp!=0
		  and Employee.IDEmp  not in (select IDEmp from view_getDepartmentManagersList)
	order by Employee.FirstName
end
go


create procedure sp_showEmployeesListInDepartment
@IDDepartment int
as
begin
	select Employee.*,Department.DepartmentName,Position.PositionName
	from Employee,Department,Position
	where Employee.DepartmentID=Department.IDDepartment and Employee.PositionID=Position.IDPosition
		  and Department.IDDepartment=@IDDepartment
		  and Employee.StatusEmp!=0
	order by Employee.FirstName
end
go


create procedure sp_getDepartmentManagerOfThisEmployee
@IDEmp int
as
begin
	select view_getDepartmentManagersList.*
	from view_getDepartmentManagersList,Employee
	where Employee.DepartmentID=view_getDepartmentManagersList.DepartmentID
		  and Employee.IDEmp=@IDEmp
		  and Employee.IDEmp!=view_getDepartmentManagersList.IDEmp
end
go


create procedure sp_getManagerOfThisDepartment
--IDPosition=1: admin
--IDPosition=2: Director
--IDPosition=3: Department Manager
--IDPosition=4: Personnel
@IDDepartment int
as
begin
	select * from Employee
	where Employee.DepartmentID=@IDDepartment and Employee.PositionID=3
end
go



create procedure sp_getAdmin
--IDPosition=1: admin
--IDPosition=2: Director
--IDPosition=3: Department Manager
--IDPosition=4: Personnel
as
begin
	select * from Employee
	where Employee.PositionID=1
end
go


create procedure sp_getDirector
--IDPosition=1: admin
--IDPosition=2: Director
--IDPosition=3: Department Manager
--IDPosition=4: Personnel
as
begin
	select * from Employee
	where Employee.PositionID=2
end
go


create procedure sp_searchEmployeeForAdmin
--IDPosition=1: admin
--IDPosition=2: Director
--IDPosition=3: Department Manager
--IDPosition=4: Personnel
@IDDepartment int,@IDPosition int,@FirstName nvarchar (25),@LastName nvarchar (25)
as
begin
	select Employee.*,Department.DepartmentName,Position.PositionName
	from Employee,Department,Position
	where Employee.DepartmentID=Department.IDDepartment and Employee.PositionID=Position.IDPosition
		  and Employee.StatusEmp!=0
		  and Position.IDPosition!=1 and Position.IDPosition!=2
		  and (Position.IDPosition=@IDPosition or @IDPosition is null)
		  and (Department.IDDepartment=@IDDepartment or @IDDepartment is null)
		  and (Employee.FirstName like '%'+@FirstName+'%' or Employee.FirstName is null)
		  and (Employee.LastName like '%'+@LastName+'%' or Employee.LastName is null)
	order by Employee.FirstName
end
go

create procedure sp_searchEmployeeForDepartmentManager
--IDPosition=1: admin
--IDPosition=2: Director
--IDPosition=3: Department Manager
--IDPosition=4: Personnel
@IDDepartment int,@FirstName nvarchar (25),@LastName nvarchar (25)
as
begin
	select Employee.*,Department.DepartmentName,Position.PositionName
	from Employee,Department,Position
	where Employee.DepartmentID=Department.IDDepartment and Employee.PositionID=Position.IDPosition
		  and Employee.StatusEmp!=0
		  and Position.IDPosition!=2 and Position.IDPosition!=3
		  and Department.IDDepartment=@IDDepartment
		  and (Employee.FirstName like '%'+@FirstName+'%' or Employee.FirstName is null)
		  and (Employee.LastName like '%'+@LastName+'%' or Employee.LastName is null)
	order by Employee.FirstName
end
go

create procedure sp_searchEmployeeForDirector
--IDPosition=1: admin
--IDPosition=2: Director
--IDPosition=3: Department Manager
--IDPosition=4: Personnel
@IDDepartment int,@IDPosition int,@FirstName nvarchar (25),@LastName nvarchar (25)
as
begin
	select Employee.*,Department.DepartmentName,Position.PositionName
	from Employee,Department,Position
	where Employee.DepartmentID=Department.IDDepartment and Employee.PositionID=Position.IDPosition
		  and Employee.StatusEmp!=0
		  and Position.IDPosition!=2
		  and (Position.IDPosition=@IDPosition or @IDPosition is null)
		  and (Department.IDDepartment=@IDDepartment or @IDDepartment is null)
		  and (Employee.FirstName like '%'+@FirstName+'%' or Employee.FirstName is null)
		  and (Employee.LastName like '%'+@LastName+'%' or Employee.LastName is null)
	order by Employee.FirstName
end
go


-------------------------------------------------------------------------------------------------
--Create Procedure for Applications


create procedure sp_InsertApplication
@EmpID int, @StartDate date, @EndDate date, @Reason nvarchar(500),@Contact nvarchar(100),
@Status int
as
begin
	insert into [Application]
	values
		(@EmpID,@StartDate,@EndDate,@Reason,@Contact,@Status)
end
go


create procedure sp_UpdateApplicationStatus
--status=0: pending approval
--status=1: approved the  leave applications
--status=2: reject  the  leave applications
--status=3: withdraw  his/her  leave  application  (which  has  not  been approved yet)
--status=4: Cancel  his/her  leave  (which  has  been  already  approved)
--status=5: approval cancel leave
--status=6: reject cancel leave
@IDApplication int, @Status int
as
begin
	update [Application] 
	set 
		[Status]=@Status
	where IDApplication=@IDApplication
end
go


create procedure sp_DeleteApplication
@IDApplication int
as
begin
	delete from [Application]
	where IDApplication=@IDApplication
end
GO

create procedure sp_getApplication
@IDApplication int
as
begin
	select [Application].*,Employee.FirstName,Employee.LastName,Employee.Gender,Employee.Birthday,Employee.JoinDate,
	Department.IDDepartment,Department.DepartmentName,Position.IDPosition,Position.PositionName
	from [Application],Employee,Department,Position
	where	[Application].EmpID=Employee.IDEmp 
			and Employee.DepartmentID=Department.IDDepartment
			and Employee.PositionID=Position.IDPosition		
			and IDApplication=@IDApplication
end
go

create procedure sp_getApplicationsNotApproveForDepartmentManager
@IDDepartment INT
as
begin
	select [Application].*,Employee.FirstName,Employee.LastName,Employee.Gender,Employee.Birthday,Employee.JoinDate,
	Department.IDDepartment,Department.DepartmentName,Position.IDPosition,Position.PositionName
	from [Application],Employee,Department,Position
	where	[Application].EmpID=Employee.IDEmp 
			and Employee.DepartmentID=Department.IDDepartment
			and Employee.PositionID=Position.IDPosition	
			AND Department.IDDepartment=@IDDepartment
			AND ([Application].[Status]=0 or [Application].[Status]=4)
			and Employee.PositionID!=3
end
go

create procedure sp_getApplicationsNotApproveForDirector
--IDPosition=1: admin
--IDPosition=2: Director
--IDPosition=3: Department Manager
--IDPosition=4: Personnel
as
begin
	select [Application].*,Employee.FirstName,Employee.LastName,Employee.Gender,Employee.Birthday,Employee.JoinDate,
	Department.IDDepartment,Department.DepartmentName,Position.IDPosition,Position.PositionName
	from [Application],Employee,Department,Position
	where	[Application].EmpID=Employee.IDEmp 
			and Employee.DepartmentID=Department.IDDepartment
			and Employee.PositionID=Position.IDPosition	
			AND Employee.PositionID=3
			AND ([Application].[Status]=0 or [Application].[Status]=4)
end
go

create procedure sp_getLastedApplication
@IDEmp int
as
begin
	select top 1 [Application].*,Employee.FirstName,Employee.LastName,Employee.Gender,Employee.Birthday,Employee.JoinDate,
	Department.IDDepartment,Department.DepartmentName,Position.IDPosition,Position.PositionName
	from [Application],Employee,Department,Position
	where	[Application].EmpID=Employee.IDEmp 
			and Employee.DepartmentID=Department.IDDepartment
			and Employee.PositionID=Position.IDPosition		
			and EmpID=@IDEmp
	order by [Application].IDApplication desc
end
go

create procedure sp_getLeaveHistory
@EmpID int
as
begin
	select * from [Application] 
	where 
		[Application].EmpID=@EmpID and ([Application].Status=1 or [Application].Status=6)
	order by [Application].IDApplication desc
end
go


create procedure sp_getReportApplicationsIndividual
@EmpID int, @FromDate date, @ToDate date
as
begin
	select [Application].*,Employee.FirstName,Employee.LastName,Employee.Gender,Employee.Birthday,Employee.JoinDate,
	Department.IDDepartment,Department.DepartmentName,Position.IDPosition,Position.PositionName,
	(datediff(day, [Application].StartDate, [Application].EndDate)+1)as NumDaysLeave
	from [Application],Employee,Department,Position
	where [Application].EmpID=Employee.IDEmp 
			and Employee.DepartmentID=Department.IDDepartment
			and Employee.PositionID=Position.IDPosition
			and [Application].EmpID=@EmpID
			and [Application].StartDate>=@FromDate and [Application].EndDate<=@ToDate
			and ([Application].Status=1 or [Application].Status=6)
	order by [Application].IDApplication desc
end
go


create procedure sp_getReportTotalDaysLeaveIndividual
@EmpID int, @FromDate date, @ToDate date
as
begin
	select TableSumDaysLeaveOfEmp.EmpID,Employee.FirstName,Employee.LastName,Employee.Gender,Employee.Birthday,Employee.JoinDate,
	Department.IDDepartment,Department.DepartmentName,Position.IDPosition,Position.PositionName,TableSumDaysLeaveOfEmp.SumDaysLeave
	from Employee,Department,Position,
		(
			select [Application].EmpID,SUM(TableNumDaysLeaveInApp.NumDaysLeave) as SumDaysLeave
			from [Application],
			(	select IDApplication,(datediff(day, [Application].StartDate, [Application].EndDate)+1)as NumDaysLeave
				from [Application]
				where [Application].StartDate>= @FromDate and [Application].StartDate<= @ToDate 
					  and ([Application].Status=1 or [Application].Status=6)
			)as TableNumDaysLeaveInApp
			where [Application].IDApplication=TableNumDaysLeaveInApp.IDApplication
			group by [Application].EmpID
		)as TableSumDaysLeaveOfEmp
	where	TableSumDaysLeaveOfEmp.EmpID=Employee.IDEmp
			and Employee.DepartmentID=Department.IDDepartment
			and Employee.PositionID=Position.IDPosition
			and TableSumDaysLeaveOfEmp.EmpID=@EmpID
	order by TableSumDaysLeaveOfEmp.SumDaysLeave
end
go



create procedure sp_getReportTotalDaysLeaveDepartment
--employee.status=0:employee to stop work (job severance)
--employee.status=1:employee is working
@IDDepartment int, @FromDate date, @ToDate date
as
begin
	select TableSumDaysLeaveOfEmp.EmpID,Employee.FirstName,Employee.LastName,Employee.Gender,Employee.Birthday,Employee.JoinDate,
	Department.IDDepartment,Department.DepartmentName,Position.IDPosition,Position.PositionName,TableSumDaysLeaveOfEmp.SumDaysLeave
	from Employee,Department,Position,
		(
			select [Application].EmpID,SUM(TableNumDaysLeaveInApp.NumDaysLeave) as SumDaysLeave
			from [Application],
			(	select IDApplication,(datediff(day, [Application].StartDate, [Application].EndDate)+1)as NumDaysLeave
				from [Application]
				where [Application].StartDate>= @FromDate and [Application].StartDate<= @ToDate 
					  and ([Application].Status=1 or [Application].Status=6)
			)as TableNumDaysLeaveInApp
			where [Application].IDApplication=TableNumDaysLeaveInApp.IDApplication
			group by [Application].EmpID
		)as TableSumDaysLeaveOfEmp
	where	TableSumDaysLeaveOfEmp.EmpID=Employee.IDEmp
			and Employee.DepartmentID=Department.IDDepartment
			and Employee.PositionID=Position.IDPosition
			and Department.IDDepartment=@IDDepartment
			and Employee.StatusEmp=1
	order by TableSumDaysLeaveOfEmp.SumDaysLeave desc
end
go


create procedure sp_getReportTotalDaysLeaveCompany
--employee.status=0:employee to stop work (job severance)
--employee.status=1:employee is working
@FromDate date, @ToDate date
as
begin
	select TableSumDaysLeaveOfEmp.EmpID,Employee.FirstName,Employee.LastName,Employee.Gender,Employee.Birthday,Employee.JoinDate,
	Department.IDDepartment,Department.DepartmentName,Position.IDPosition,Position.PositionName,TableSumDaysLeaveOfEmp.SumDaysLeave
	from Employee,Department,Position,
		(
			select [Application].EmpID,SUM(TableNumDaysLeaveInApp.NumDaysLeave) as SumDaysLeave
			from [Application],
			(	select IDApplication,(datediff(day, [Application].StartDate, [Application].EndDate)+1)as NumDaysLeave
				from [Application]
				where [Application].StartDate>= @FromDate and [Application].StartDate<= @ToDate 
					  and ([Application].Status=1 or [Application].Status=6)
			)as TableNumDaysLeaveInApp
			where [Application].IDApplication=TableNumDaysLeaveInApp.IDApplication
			group by [Application].EmpID
		)as TableSumDaysLeaveOfEmp
	where	TableSumDaysLeaveOfEmp.EmpID=Employee.IDEmp
			and Employee.DepartmentID=Department.IDDepartment
			and Employee.PositionID=Position.IDPosition
			and Employee.StatusEmp=1
	order by TableSumDaysLeaveOfEmp.SumDaysLeave desc
end
go


-------------------------------------------------------------------------------------------------
--Create Procedure For Holiday


create procedure sp_InsertHoliday
@StartDate date, @EndDate date, @Event nvarchar(200)
as
begin
	insert into Holiday
	values
		(@StartDate,@EndDate,@Event)
end
go


create procedure sp_InsertConfiguration
@IDConfiguration int,@Description nvarchar(100),@Parameter int
as
begin
	insert into Configuration
	values
		(@IDConfiguration,@Description,@Parameter)
end
go


create procedure sp_UpdateHoliday
@IDHoliday int, @StartDate date, @EndDate date, @Event nvarchar(200)
as
begin
	update Holiday
	set 
		StartDate=@StartDate,
		EndDate=@EndDate,
		[Event]=@Event
	where IDHoliday=@IDHoliday	
end
go


create procedure sp_DeleteHoliday
@IDHoliday int
as
begin
	delete from Holiday
	where IDHoliday=@IDHoliday
end
go


create procedure sp_getHoliday
@IDHoliday int
as
begin
	select * from Holiday
	where IDHoliday=@IDHoliday	
end
go


create procedure sp_getHolidayList
as
begin
	select * from Holiday
end
go


create procedure sp_getUpcomingHolidays
@CurrentDate date
as
begin
	select * from Holiday
	where StartDate>=@CurrentDate
end
go


-------------------------------------------------------------------------------------------------
--Create Procedure For Configuration


create procedure sp_UpdateConfigParameter
@IDConfiguration int, @Parameter int
as
begin
	update Configuration 
	set
		Parameter=@Parameter
	where IDConfiguration=@IDConfiguration
end
go


create procedure sp_getConfiguration
--Configuration ID = 1: Maximum number ofdays in one year is allowed for leaving.
--Configuration ID = 2: the time, which you apply leave application,  must before the day which you leave is [n] days
--Configuration ID = 3: The number of records displayed in the one data table on the form.("The minimum period " which use to view and browse the leave application)
@IDConfiguration int
as
begin
	select * from Configuration where IDConfiguration=@IDConfiguration
end
go

create procedure sp_showConfigurationsList
as
begin
	select * from Configuration
end
go

-----------------------------------------------------------------------------
--Insert data demo


INSERT INTO Department
VALUES
	('Business Department'),
	('Technical Department'),
	('HR Department')
GO

INSERT INTO Position
VALUES
	(1,'Admin'),
	(2,'Director'),
	(3,'Department Manager'),
	(4,'Personnel')
GO


INSERT INTO Employee
(FirstName,LastName,Gender,Birthday,[Address],Email,Phone,DepartmentID,PositionID,JoinDate,UserName,[Password],StatusEmp)
VALUES
	('Tan','Nguyen','True','1992/12/27','354 Dai Lo Binh Duong','nguyenvantan27binhduong@gmail.com','01265154373',1,3,'2009/8/20','nguyenvantan','1234567',1),
	('Hung','Hoang','True','1993/1/1','50/22 Do Tan Phong','lord_of_destruction00@yahoo.com','0903987654',2,3,'2009/12/20','hoangkhanhhung','1234567',1),
	('Hung','Pham','True','1992/10/10','354 Bien Hoa','phamngochung@gmail.com','09584587747',3,3,'2009/9/19','phamngochung','1234567',1),

	('Hieu','Tran','True','1986/6/15','120/12 Phan Dang Luu','tranchihieu@gmail.com','090352146',1,4,'2009/1/1','tranchihieu','1234567',1),
	('Trung','Bui','True','1993/5/27','120/46 Phan Xich Long','buiminhtrung@gmail.com','0903185426',1,4,'2008/4/1','buiminhtrung','1234567',1),
	('Son','Do','True','1993/9/12','52 D2 Binh Thanh','dominhson@gmail.com','021546312',1,4,'2009/5/13','dominhson','1234567',1),
	('Nhi','Dang','False','1993/4/10','254 Nguyen Trai','danghoangyennhi@yahoo.com','01254587458',1,4,'2008/1/25','danghoangyennhi','1234567',1),

	('Tri','Do','True','1993/9/28','95/24/52 D3 Hang Xanh','docaotri@gmail.com','090325146',2,4,'2009/2/2','docaotri','1234567',1),
	('Anh','Trinh','True','1990/12/9','511/01 D4 Hanh Do','trinhhunganh@gmail.com','09325781',2,4,'2009/4/18','trinhhunganh','1234657',1),
	('Hoang','Truong','True','1991/1/25','52/421/112 Phan Huy Ich','truongviethoang@gmail.com','03926514',2,4,'2008/1/15','truongviethoang','1234567',1),
	('Trang','Nguyen','False','1993/5/10','26 DT734 Thuan An','nguyenthitrang@yahoo.com','0987545222',2,4,'2009/4/20','nguyenthitrang','1234567',1),
	
	('Chau','Huynh','True','1993/10/14','152/42 Tran Khac Chan','huynhnguyenchau@yahoo.com','0903415978',3,4,'2008/12/8','huynhnguyenchau','1234567',1),
	('Duc','Vu','True','1992/9/4','92/11 Phan Dinh Phung','vuminhduc@gmail.com','0903284385',3,4,'2009/11/19','vuminhduc','1234567',1),
	('Nhung','Nguyen','False','1993/1/19','123 Binh Dang-Binh Hoa','nguyenthinhung@yahoo.com','01254144747',3,4,'2009/5/6','nguyenthinhung','1234567',1),
	('Inmad','Nimda','True','1993/1/1','128bis Nguyen Thi Minh Khai','inmadnimda@gmail.com','01254141414',3,1,'2007/1/1','admin','1234567',1),

	('Toredi','Dorectir','True','1990/12/4','24 Ly Thuong Kiet','director','1234567',null,2,'2007/1/1','director','1234567',1)

GO





INSERT INTO [Application]
--status=0: pending approval
--status=1: approved the  leave applications
--status=2: reject  the  leave applications
--status=3: withdraw  his/her  leave  application  (which  has  not  been approved yet)
--status=4: Cancel  his/her  leave  (which  has  been  already  approved)
--status=5: approval cancel leave
--status=6: reject cancel leave
VALUES
	(1,'2010/5/1','2010/5/2','Wedding','Number phone:xxxxxxxxxx0',1),
	(1,'2010/10/10','2010/10/11','own busy','Number phone:xxxxxxxxxx0',2),
	(1,'2011/6/2','2011/6/5','party','Number phone:xxxxxxxxxx0',3),
	(1,'2011/12/1','2011/12/7','fostering knowledge','Number phone:xxxxxxxxxx0',1),
	(1,'2012/1/15','2012/1/19','own busy','Number phone:xxxxxxxxxx0',5),
	(1,'2012/5/4','2012/5/7','own busy','Number phone:xxxxxxxxxx0',1),
	(1,'2012/11/5','2012/11/6','own busy','Number phone:xxxxxxxxxx0',0),
	
	(2,'2010/2/1','2010/2/2','own busy','Address:123 Abc Xyz',1),
	(2,'2010/9/5','2010/9/7','own busy','Address:123 Abc Xyz',2),
	(2,'2011/3/3','2011/3/6','Wedding','Address:123 Abc Xyz',1),
	(2,'2011/5/1','2011/5/2','own busy','Address:123 Abc Xyz',6),
	(2,'2011/11/20','2011/11/22','party','Address:123 Abc Xyz',3),
	(2,'2012/4/1','2012/4/2','fostering knowledge','Address:123 Abc Xyz',5),
	(2,'2012/11/10','2012/11/12','own busy','Address:123 Abc Xyz',0),
	
	(3,'2010/1/19','2010/1/22','fostering knowledge','Number phone:01258547584',1),
	(3,'2010/3/9','2010/3/10','own busy','Address:123 Avenue freedom-Thuan An',1),
	(3,'2010/8/27','2010/8/28','own busy','Number phone:01258547584',2),
	(3,'2011/3/9','2011/3/13','party','Address:123 Avenue freedom-Thuan An',3),
	(3,'2011/10/7','2011/10/9','Wedding','Number phone:01258547584',1),
	(3,'2012/6/5','2012/6/6','own busy','Address:123 Avenue freedom-Thuan An',6),
	(3,'2012/11/20','2012/11/21','own busy','Number phone:01258547584',0),
	
	
	(4,'2010/3/25','2010/3/26','fostering knowledge','Number phone:01258547584',6),
	(4,'2010/3/7','2010/3/8','party','Address:123s 550 Street-Thuan An',1),
	(4,'2010/8/15','2010/8/16','own busy','Number phone:01475858585',2),
	(4,'2011/3/7','2011/3/9','party','Address:123s 550 Street-Thuan An',3),
	(4,'2011/10/12','2011/10/14','Wedding','Number phone:01475858585',1),
	(4,'2012/6/27','2012/6/28','fostering knowledge','Address:123s 550 Street-Thuan An',5),
	(4,'2012/11/11','2012/11/12','own busy','Number phone:01475858585',0),
	
	(5,'2010/5/6','2010/5/7','Wedding','Number phone:02563636363',1),
	(5,'2010/10/19','2010/10/20','own busy','Number phone:02563636363',6),
	(5,'2011/6/21','2011/6/22','party','Number phone:02563636363',2),
	(5,'2011/12/5','2011/12/8','fostering knowledge','Number phone:02563636363',1),
	(5,'2012/1/28','2012/1/29','own busy','Number phone:02563636363',1),
	(5,'2012/5/1','2012/5/2','own busy','Number phone:02563636363',2),
	(5,'2012/11/11','2012/11/12','own busy','Number phone:02563636363',0),
	
	(6,'2010/1/3','2010/1/4','fostering knowledge','Number phone:01452636363',1),
	(6,'2010/3/7','2010/3/8','own busy','Address:123 Avenue freedom-Thu Dau Mot',2),
	(6,'2010/8/12','2010/8/14','own busy','Number phone:01452636363',1),
	(6,'2011/3/18','2011/3/20','party','Address:123 Avenue freedom-Thu Dau Mot',3),
	(6,'2011/10/8','2011/10/9','Wedding','Number phone:01452636363',1),
	(6,'2012/6/5','2012/6/6','own busy','Address:123 Avenue freedom-Thu Dau Mot',6),
	(6,'2012/11/15','2012/11/16','own busy','Number phone:01452636363',0),
	
	(7,'2010/5/8','2010/5/9','Wedding','Number phone:01478526963',1),
	(7,'2010/10/19','2010/10/21','own busy','Number phone:01478526963',6),
	(7,'2011/6/24','2011/6/25','party','Number phone:01478526963',3),
	(7,'2011/10/17','2011/10/18','fostering knowledge','Number phone:01478526963',1),
	(7,'2011/12/15','2011/12/16','fostering knowledge','Number phone:01478526963',5),
	(7,'2012/6/5','2012/6/6','own busy','Address:456 DT747-Thuan An',1),
	(7,'2012/11/13','2012/11/14','own busy','Number phone:01478526963',0),
	
	(8,'2010/5/6','2010/5/7','Wedding','Number phone:02569858563',1),
	(8,'2010/10/25','2010/10/26','own busy','Number phone:02569858563',2),
	(8,'2011/3/27','2011/3/28','party','Address:352 DT747-Thuan An',3),
	(8,'2011/10/13','2011/10/14','Wedding','Number phone:02569858563',1),
	(8,'2012/6/21','2012/6/22','fostering knowledge','Address:1352 DT747-Thuan An',5),
	(8,'2012/10/11','2012/10/12','own busy','Number phone:02569858563',1),
	(8,'2012/11/7','2012/11/8','own busy','Number phone:02569858563',0),
	
	(9,'2010/5/15','2010/5/18','Wedding','Number phone:0325262626',1),
	(9,'2010/8/17','2010/8/18','own busy','Number phone:0325262626',2),
	(9,'2010/10/1','2010/10/2','own busy','Number phone:0325262626',2),
	(9,'2011/3/6','2011/3/9','party','Address:129sa 550 Street-Thuan An',3),
	(9,'2011/12/5','2011/12/7','fostering knowledge','Number phone:0325262626',1),
	(9,'2012/1/20','2012/1/25','own busy','Number phone:0325262626',1),
	(9,'2012/11/8','2012/11/9','own busy','Number phone:0325262626',0),
	
	(10,'2010/5/15','2010/5/18','Wedding','Number phone:0325262626',1),
	(10,'2010/8/17','2010/8/18','own busy','Number phone:0325262626',2),
	(10,'2010/10/1','2010/10/2','own busy','Number phone:0325262626',2),
	(10,'2011/3/6','2011/3/9','party','Address:129sa 550 Street-Thuan An',3),
	(10,'2011/12/5','2011/12/7','fostering knowledge','Number phone:0325262626',1),
	(10,'2012/1/20','2012/1/25','own busy','Number phone:0325262626',1),
	(10,'2012/11/8','2012/11/9','own busy','Number phone:0325262626',0),
	
	(11,'2010/5/22','2010/5/23','Wedding','Number phone:0958741236',1),
	(11,'2010/8/17','2010/8/19','own busy','Number phone:0958741236',2),
	(11,'2010/10/1','2010/10/3','own busy','Number phone:0958741236',3),
	(11,'2011/3/11','2011/3/12','own busy','Address:567 Avenue freedom-Thuan An',5),
	(11,'2011/8/24','2011/8/25','own busy','Number phone:0958741236',1),
	(11,'2012/3/9','2012/3/10','party','Address:567 Avenue freedom-Thuan An',1),
	(11,'2012/11/7','2012/11/8','Wedding','Number phone:0958741236',0),
	
	(12,'2010/1/10','2010/1/11','fostering knowledge','Number phone:01258547584',1),
	(12,'2010/3/19','2010/3/21','own busy','Address:123 Avenue freedom-Thuan An',1),
	(12,'2010/8/29','2010/8/30','own busy','Number phone:01258547584',2),
	(12,'2011/3/1','2011/3/2','party','Address:129sa 550 Street-Thuan An',6),
	(12,'2011/12/6','2011/12/7','fostering knowledge','Number phone:0325262626',5),
	(12,'2012/5/9','2012/5/11','own busy','Number phone:02563636363',1),
	(12,'2012/11/15','2012/11/17','own busy','Number phone:02563636363',0),
	
	(13,'2010/3/19','2010/3/21','own busy','Address:123 DT742-Thuan An',1),
	(13,'2010/5/1','2010/5/2','Wedding','Number phone:0154523652',1),
	(13,'2010/10/10','2010/10/11','own busy','Number phone:0154523652',2),
	(13,'2011/3/7','2011/3/9','party','Address:123 DT742-Thuan An',3),
	(13,'2011/10/12','2011/10/14','Wedding','Number phone:0154523652',1),
	(13,'2012/6/5','2012/6/6','own busy','Address:123 Avenue freedom-Thu Dau Mot',6),
	(13,'2012/11/15','2012/11/16','own busy','Number phone:0154523652',0),
	
	(14,'2010/2/2','2010/2/3','own busy','Address:123 Abc Xyz',1),
	(14,'2010/9/6','2010/9/8','own busy','Address:123 Abc Xyz',1),
	(14,'2010/10/22','2010/10/23','own busy','Number phone:0325262626',2),
	(14,'2011/3/7','2011/3/8','party','Address:129sa 550 Street-Thuan An',3),
	(14,'2011/12/25','2011/12/27','fostering knowledge','Number phone:0325262626',5),
	(14,'2012/5/4','2012/5/5','own busy','Number phone:02563636363',6),
	(14,'2012/11/17','2012/11/19','fostering knowledge','Number phone:02563636363',0),
	
	(15,'2010/5/8','2010/5/9','party','Number phone:0569856745',2),
	(15,'2010/10/28','2010/10/29','Wedding','Number phone:0569856745',1),
	(15,'2011/6/2','2011/6/3','party','Number phone:0569856745',1),
	(15,'2011/10/17','2011/10/18','fostering knowledge','Number phone:0569856745',3),
	(15,'2011/12/25','2011/12/26','fostering knowledge','Number phone:0569856745',5),
	(15,'2012/1/20','2012/1/22','own busy','Number phone:0569856745',6),
	(15,'2012/11/17','2012/11/19','own busy','Number phone:0569856745',0)
GO

insert into Holiday
(StartDate,EndDate,[Event])
values
	('2012/11/30','2012/12/1','Le 30 thang 4')
	
insert into Configuration
(IDConfiguration,[Description],Parameter)
values
	(1,'Max Number of Dates allow to leave',10)
	


select * from Configuration

select * from Employee















